package com.group4.DB;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.group4.DAO.ForumDAO;

public class Forum {
	
	private int ID;
	private String forumHeadText;
	private int forumOwnerId;
	private Date forumDateTime;

	private static ResultSet rs = null;
	private static PreparedStatement ps = null;
	
	public static ForumDAO getForumWithID(int ID) {
		ForumDAO forum = null;
		try {
			ps = ConnectionManager.conn.prepareStatement("select * from tbl_forum where ID = ?");

			ps.setInt(1, ID);

			rs = ps.executeQuery();

			while (rs.next()) {
				forum = new ForumDAO();
				forum.setID(ID);
				forum.setForumHeadText(rs.getString("forum_headText"));
				forum.setForumOwnerId(rs.getInt("forum_ownerId"));
				forum.setForumDateTime(rs.getDate("forum_dateTime"));
			}
		} catch (SQLException e) {
			System.out.println("SQL Error in getting forum with id");
			e.printStackTrace();
		} catch (Exception e) {
			System.out.println("Error in getting forum with id");
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					System.out.println("SQL Error in getting forum with id in finally");
					e.printStackTrace();
				}
			}
		}
		return forum;
	}
	
	public static List<ForumDAO> getAllForum() {
		List<ForumDAO> forumList = new ArrayList<ForumDAO>();
		ForumDAO forum = null;
		try {
			ps = ConnectionManager.conn.prepareStatement("select * from tbl_forum");


			rs = ps.executeQuery();

			while (rs.next()) {
				forum = new ForumDAO();
				forum.setID(rs.getInt("ID"));
				forum.setForumHeadText(rs.getString("forum_headText"));
				forum.setForumOwnerId(rs.getInt("forum_ownerId"));
				java.util.Date date  = rs.getTimestamp("forum_dateTime"); 
				forum.setForumDateTime(new Date(date.getTime()));
				forumList.add(forum);
			}
		} catch (SQLException e) {
			System.out.println("SQL Error in getting forum with id");
			e.printStackTrace();
		} catch (Exception e) {
			System.out.println("Error in getting forum with id");
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					System.out.println("SQL Error in getting forum with id in finally");
					e.printStackTrace();
				}
			}
		}
		return forumList;
	}

	public static ForumDAO getForum(String headText) {
		ForumDAO forum = null;
		try {
			ps = ConnectionManager.conn.prepareStatement("select * from tbl_forum where forum_headText = ?");

			ps.setString(1, headText);

			rs = ps.executeQuery();

			while (rs.next()) {
				forum = new ForumDAO();
				forum.setID(rs.getInt("ID"));
				forum.setForumHeadText(rs.getString("forum_headText"));
				forum.setForumOwnerId(rs.getInt("forum_ownerId"));
				forum.setForumDateTime(rs.getDate("forum_dateTime"));
			}
		} catch (SQLException e) {
			System.out.println("SQL Error in getting forum with headText");
			e.printStackTrace();
		} catch (Exception e) {
			System.out.println("Error in getting forum with headText");
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					System.out.println("SQL Error in getting forum with headText in finally");
					e.printStackTrace();
				}
			}
		}
		return forum;
	}

	
	public static boolean addForum(ForumDAO forum) {

		boolean result = false;

		if (ConnectionManager.conn == null) {
			System.out.println("connection is null");
			return result;
		}

		PreparedStatement ps = null;
		try {
			ps = ConnectionManager.conn
					.prepareStatement(" insert into tbl_forum (forum_headText, forum_ownerId, forum_dateTime)"
							+ " values (?, ?, ?)");

			ps.setString(1, forum.getForumHeadText());
			ps.setInt(2, forum.getForumOwnerId());
			Timestamp time = new Timestamp(forum.getForumDateTime().getTime());
			ps.setTimestamp(3, time);

			ps.executeUpdate();
			
			ResultSet generatedKeys = ps.getGeneratedKeys();
			if (generatedKeys.next()) {
				forum.setID(generatedKeys.getInt(1));
			} else {
				throw new SQLException("Creating forum failed, no ID obtained.");
			}


			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}

	public boolean updateForum(ForumDAO forum) {

		boolean result = false;

		if (ConnectionManager.conn == null) {
			System.out.println("Connection is null");
			return result;
		}
		PreparedStatement ps = null;
		try {
			ForumDAO oldForum = getForumWithID(forum.getID());

			if (oldForum == null) {
				System.out.println("No forum found with ID = " + forum.getID());
				return result;
			}

			ps = ConnectionManager.conn
					.prepareStatement("update tbl_forum set forum_headText = ?, forum_ownerId = ?, forum_dateTime = ? where ID = ?");

			ps.setString(1, forum.getForumHeadText());
			ps.setInt(2, forum.getForumOwnerId());
			ps.setDate(3, forum.getForumDateTime());
			ps.setInt(3, forum.getID());

			ps.executeUpdate();

			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}

	public static boolean deleteForum(int ID) {
		boolean result = false;
		
		if (ConnectionManager.conn == null) {
			System.out.println("Connection is null");
			return result;
		}

		PreparedStatement ps = null;
		Forum Forum = null;

		try {
			ForumDAO oldForum = getForumWithID(ID);

			if (oldForum == null) {
				System.out.println("No Forum found with ID = "+ ID);
				return result;
			}
			
			ps = ConnectionManager.conn.prepareStatement("delete from tbl_forum where ID = ?");

			ps.setInt(1, ID);

			ps.execute();
			
			result = true;

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}
	
	@Override
	public String toString() {
		StringBuffer sb = new StringBuffer();

		sb.append("Forum [ID:").append(ID);
		sb.append("; forum_headText:").append(forumHeadText);
		sb.append("; forum_ownerId:").append(forumOwnerId);
		sb.append("; forum_dateTime:").append(forumDateTime);
		sb.append("]");

		return sb.toString();
	}
}